package com.jeesite.modules.stock.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class DataTrans {

    protected Logger log = LoggerFactory.getLogger(DataTrans.class);

    @Value("${dataTrans.sourceDb.url}")
    private String sourceUrl;

    @Value("${dataTrans.sourceDb.username}")
    private String sourceUser;

    @Value("${dataTrans.sourceDb.password}")
    private String sourcePass;

    @Value("${dataTrans.targetDb.url}")
    private String targetUrl;

    @Value("${dataTrans.targetDb.username}")
    private String targetUser;

    @Value("${dataTrans.targetDb.password}")
    private String targetPass;


    public void dataTrans(String tableName, String whereClause) {
        int targetSize = targetTableDataSize(tableName, whereClause);
        if (targetSize > 0) {
            return;
        }
        Set<String> columns = new HashSet<>();
        List<Map<String, Object>> datas = sourceData(tableName, whereClause, columns);
        if (!datas.isEmpty()) {
            tran2Target(tableName, columns, datas);
        }
    }

    public boolean dataChange() {
        String sdate = getLastStockDate("source");
        String tdate = getLastStockDate("target");
        if (sdate == null || tdate == null) {
            return false;
        }
        return !Objects.equals(sdate, tdate);
    }

    private String getLastStockDate(String type) {
        Connection conn = null;
        PreparedStatement statm = null;
        ResultSet rs = null;
        SimpleDateFormat fr = new SimpleDateFormat("yyyy-MM-dd");
        try {
            if ("source".equalsIgnoreCase(type)) {
                conn = DriverManager.getConnection(sourceUrl, sourceUser, sourcePass);
            } else {
                conn = DriverManager.getConnection(targetUrl, targetUser, targetPass);
            }
            String sql = "select date from stock_latest_data where stock_code='000001'";
            statm = conn.prepareStatement(sql);
            rs = statm.executeQuery();
            if (rs.next()) {
                Date date = rs.getDate("date");
                return fr.format(date);
            }
        } catch (Exception e) {
            log.error("查询数据失败，errMsg: {}", e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statm != null) {
                    statm.close();
                }
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
        return null;
    }

    public void deleteData(String tableName, String whereClause) {
        Connection conn = null;
        PreparedStatement statm = null;
        try {
            conn = DriverManager.getConnection(targetUrl, targetUser, targetPass);
            String sql = "delete from " + tableName + " where " + whereClause;
            statm = conn.prepareStatement(sql);
            int i = statm.executeUpdate();
            if (i <= 0) {
                log.error("删除数据失败，errMsg: {}", "executeUpdate返回:" + i);
            }
        } catch (Exception e) {
            log.error("删除数据失败，errMsg: {}", e.getMessage(), e);
        } finally {
            try {
                if (statm != null) {
                    statm.close();
                }
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    private int targetTableDataSize(String tableName, String whereClause) {
        Connection conn = null;
        PreparedStatement statm = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(targetUrl, targetUser, targetPass);
            String sql = "select count(1) as c from " + tableName + " where " + whereClause;
            statm = conn.prepareStatement(sql);
            rs = statm.executeQuery();
            if (rs.next()) {
                return rs.getInt("c");
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statm != null) {
                    statm.close();
                }
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
        return 0;
    }

    private void removeExistData(String tableName, List<Map<String, Object>> datas) {
        List<String> existIds = new ArrayList<>();
        int step = (datas.size() - 1) / 100 + 1;
        for (int i = 0; i < step; i++) {
            int offset = i * 100;
            int toIndex = Math.min(offset + 100, datas.size());
            List<String> ids = removeExistData(tableName, datas, offset, toIndex);
            existIds.addAll(ids);

        }
        datas.removeIf(item -> existIds.contains(item.get("id").toString()));
    }

    private List<String> removeExistData(String tableName, List<Map<String, Object>> datas, int offset, int toIndex) {
        Connection conn = null;
        PreparedStatement statm = null;
        ResultSet rs = null;
        try {
            List<Map<String, Object>> subDatas = datas.subList(offset, toIndex);
            String ids = subDatas.stream().map(item -> item.get("id").toString())
                    .collect(Collectors.joining(","));
            conn = DriverManager.getConnection(targetUrl, targetUser, targetPass);
            String sql = "select id from " + tableName + " where id in(" + ids + ")";
            statm = conn.prepareStatement(sql);
            rs = statm.executeQuery();
            List<String> existIds = new ArrayList<>();
            while (rs.next()) {
                int id = rs.getInt("id");
                existIds.add(String.valueOf(id));
            }
            return existIds;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statm != null) {
                    statm.close();
                }
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
        return Collections.emptyList();
    }

    public void tran2Target(String tableName, Set<String> columns, List<Map<String, Object>> datas) {
        Connection conn = null;
        PreparedStatement statm = null;
        try {
            removeExistData(tableName, datas);
            if (datas.isEmpty()) {
                return;
            }
            conn = DriverManager.getConnection(targetUrl, targetUser, targetPass);
            // 3、创建statement对象,这个对象是可以发送sql
            List<String> sortColumns = new ArrayList<>(columns);
            List<String> qos = new ArrayList<>();
            sortColumns.forEach(item -> qos.add("?"));
            //4、发送sql
            if (!datas.isEmpty()) {
                String sql = "insert into " + tableName + "(" + String.join(",", sortColumns) + ")values(" + String.join(",", qos) + ")";
                statm = conn.prepareStatement(sql);
                int index = 0;
                for (Map<String, Object> data : datas) {
                    for (int i = 0; i < sortColumns.size(); i++) {
                        statm.setObject(i + 1, data.get(sortColumns.get(i)));
                    }
                    statm.addBatch();
                    index++;
                    if (index % 1000 == 0) {
                        statm.executeBatch();
                        statm.clearBatch();
                    }
                }
                statm.executeBatch();
                statm.clearBatch();
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (statm != null) {
                    statm.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    public List<Map<String, Object>> sourceData(String tableName, String whereClase, Set<String> columns) {
        List<Map<String, Object>> list = new ArrayList<>();

        Connection conn = null;
        Statement statm = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(sourceUrl, sourceUser, sourcePass);
            // 3、创建statement对象,这个对象是可以发送sql
            statm = conn.createStatement();
            //4、发送sql
            String sql = "select * from " + tableName + " where " + whereClase;
            rs = statm.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            //5、处理结果集
            while (rs.next()) {//指针一个一个的去找，如果找到就取出
                Map<String, Object> data = new HashMap<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    Object value = rs.getObject(i);
                    String columnName = metaData.getColumnName(i);
                    if (columnName.startsWith("outperform") || columnName.startsWith("close_MAX")) {
                        continue;
                    }
                    data.put(columnName, value);
                    columns.add(columnName);
                }
                list.add(data);
            }
            // 6、关闭连接 后打开的先关闭
            System.out.println();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statm != null) {
                    statm.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
        return list;
    }
}
